import json
from typing import List

from flask_sqlalchemy import SQLAlchemy
from modules.dev.enums.schema_form_type_enum import SchemaFormTypeEnum
from modules.dev.enums.schema_table_type_enum import SchemaTableTypeEnum
from modules.dev.models.schema import Schema
from modules.dev.models.schema_field import SchemaField
from modules.sys.enums.yes_no_enum import YesNoEnum
from packages.core.db.decorators import Transactional
from packages.core.auth.login_user_holder import LoginUserHolder
from modules.dev.services.schema_field_service import SchemaFieldService
from packages.tools.base_tool import generate_id, underline_to_camel, underline_to_hump
from packages.tools.db_tool import DbTool
from packages.common.base import IdForm, IdsForm
from modules.dev.validators.schema_validator import SchemaForm, SchemaPageForm


class SchemaService():
    """
    数据模型模块业务处理类
    """
    def __init__(self, db: SQLAlchemy):
        self.model = Schema
        self.db = db
        self.schema_field_service = SchemaFieldService(db)

    def detail(self, form: IdForm):
        """
        模型详情
        """
        model = self.db.session.query(self.model).get(form.id.data)
        if model is None:
            return {}
        return model.to_dict(SchemaService.to_vo)

    def page(self, form: SchemaPageForm):
        # 重写分页查询，追加roleIds字段
        pageNum = form.data.get("pageNum", 1)
        pageSize = form.data.get("pageSize", 10)
        page = DbTool.filter_by_custom(self.model).paginate(page=pageNum, per_page=pageSize, error_out=False)
        res = self.model.to_page(page, SchemaService.to_vo)
        return res

    @Transactional()
    def save(self, form: SchemaForm):
        """
        添加数据模型
        :param form:
        :return:
        """
        model = self.model(**form.data)
        model.id = generate_id()
        model.createUser = LoginUserHolder.get_user_id()
        model.updateUser = LoginUserHolder.get_user_id()
        model.variable = json.dumps(form.ext.data, ensure_ascii=False)
        self.db.session.add(model)

    @Transactional()
    def update(self, form: SchemaForm):
        """
        修改数据模型
        :param form:
        :return:
        """
        model = self.model(**form.data)
        model.updateUser = LoginUserHolder.get_user_id()
        model.variable = json.dumps(form.ext.data, ensure_ascii=False)
        self.db.session.query(self.model).filter_by(id=form.id.data).update(
            model.to_dict()
        )

    @Transactional()
    def remove(self, form: IdsForm):
        """
        删除
        :param form:
        :return:
        """
        self.model().remove(self.model, form.ids.data)

    def get_by_table_name(self, tableName):
        data = self.db.session.query(self.model).filter_by(tableName=tableName).first()
        if data is not None:
            form = IdForm(data={
                "id": data.id,
            })
            return self.wrapper(self.detail(form))
        return {}

    def db_table(self):
        """
        获取数据库表结构（仅返回表名和注释）
        :param keywords: 表名关键字
        :return: [{"name": "表名", "comment": "表注释"}, ...]
        """
        from sqlalchemy import MetaData
        # 获取数据库引擎
        engine = self.db.engine
        metadata = MetaData()
        # 反射数据库表结构
        metadata.reflect(bind=engine)
        result = []
        # 查询biz_schema表中存在的表名
        existing_tables = set()
        schema_records = self.db.session.query(Schema).all()
        for record in schema_records:
            existing_tables.add(record.tableName)
        for table in metadata.tables.values():
            # 检查表名是否在biz_schema表中存在
            is_disabled = table.name in existing_tables
            result.append({
                "name": table.name,
                "comment": table.comment or "",
                "disabled": is_disabled
            })
        return result

    @Transactional()
    def import_table(self, schema_group_id: int = None, table_names: List[str] = None):
        """
        导入数据库表为模型
        :param schema_group_id: 模型分组ID
        :param table_names: 表名列表
        :return:
        """
        if not table_names:
            return

        # 获取数据库元数据
        from sqlalchemy import MetaData
        engine = self.db.engine
        metadata = MetaData()
        metadata.reflect(bind=engine)

        # 查找已存在的模型
        existing_schemas = self.db.session.query(Schema).filter(Schema.tableName.in_(table_names)).all()
        # 删除已存在的模型及其字段
        if existing_schemas:
            existing_schema_ids = [schema.id for schema in existing_schemas]
            # 删除字段
            self.db.session.query(SchemaField).filter(SchemaField.schemaId.in_(existing_schema_ids)).delete(synchronize_session=False)
            # 删除模型
            for schema_id in existing_schema_ids:
                self.db.session.query(Schema).filter_by(id=schema_id).delete()

        # 创建新的模型和字段
        for table_name in table_names:
            if table_name not in metadata.tables:
                continue
            table = metadata.tables[table_name]
            # 创建模型
            schema = Schema()
            schema.id = generate_id()
            if schema_group_id:
                schema.schemaGroupId = schema_group_id
            schema.tableName = table_name
            schema.tableType = SchemaTableTypeEnum.SINGLE_TABLE.value[0]  # 默认单表
            schema.formType = SchemaFormTypeEnum.POP_UP_WINDOW.value[0]  # 默认弹窗
            schema.isTree = YesNoEnum.NO.value[0]  # 默认非树形
            schema.remark = table.comment if table.comment else table_name
            # 处理字段
            schema_fields = []
            field_names = []
            sort = 0
            for column in table.columns:
                # 跳过逻辑删除字段
                if column.name in ["is_deleted"]:
                    continue
                schema_field = SchemaField()
                schema_field.id = generate_id()
                schema_field.schemaId = schema.id
                schema_field.fieldName = column.name
                schema_field.remark = column.comment if column.comment else column.name
                schema_field.sort = sort
                sort += 1
                schema_field.component = "Input"  # 默认组件
                # 设置是否主键
                schema_field.isPrimary = YesNoEnum.YES.value[0] if column.primary_key else YesNoEnum.NO.value[0]
                # 设置是否可为空
                schema_field.nullable = YesNoEnum.YES.value[0] if column.nullable else YesNoEnum.NO.value[0]
                # 设置字段长度
                if hasattr(column.type, 'length'):
                    schema_field.fieldSize = column.type.length
                elif hasattr(column.type, 'display_width'):
                    schema_field.fieldSize = column.type.display_width
                # 设置默认值
                if column.default is not None:
                    schema_field.defaultValue = str(column.default)
                # 设置数据类型（简化处理，实际应该有更复杂的映射）
                type_str = str(column.type).lower()
                if 'int' in type_str:
                    schema_field.dataType = "12"  # INTEGER
                elif 'varchar' in type_str or 'text' in type_str:
                    schema_field.dataType = "17"  # STRING
                elif 'datetime' in type_str or 'timestamp' in type_str:
                    schema_field.dataType = "27"  # LOCAL_DATE_TIME
                elif 'date' in type_str:
                    schema_field.dataType = "23"  # LOCAL_DATE
                elif 'decimal' in type_str or 'numeric' in type_str:
                    schema_field.dataType = "33"  # BIG_DECIMAL
                elif 'bigint' in type_str:
                    schema_field.dataType = "13"  # LONG
                else:
                    schema_field.dataType = "17"  # STRING 默认
                # 设置扩展属性
                schema_field.variable = json.dumps({})
                schema_fields.append(schema_field)
                field_names.append(column.name)
            # 设置默认列表字段
            schema.listKeys = ",".join(field_names)
            # 设置模型扩展属性
            variable = {
                "defaultAuthTypeList": ["list", "save", "remove", "update", "detail"]
            }
            schema.variable = json.dumps(variable)
            # 保存模型和字段
            self.db.session.add(schema)
            for field in schema_fields:
                self.db.session.add(field)

    @staticmethod
    def to_vo(data):
        data["ext"] = json.loads(data["variable"])
        tableName = data["tableName"]
        if "_" not in tableName:
            data["moduleName"] = ""
            return data
        data["moduleName"] = tableName.split("_")[0]
        data["tableCamelName"] = underline_to_camel(tableName.split("_")[1])
        data["className"] = underline_to_hump(tableName.split("_")[1])
        return data

    def wrapper(self, data):
        # 追加模型字段列表
        data['columns'] = self.schema_field_service.list_by_schema_id(data['id'])
        return data
